<?php

namespace app\service;


use app\model\EntityEpisodeRelationModel;
use app\model\EntityModel;
use app\model\ProjectMemberModel;
use app\model\PlanModel;
use app\tool\SingletonTrait;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;
use support\Request;

class ProjectService
{
    use SingletonTrait;

    // 项目的不同状态
    const PROJECT_STATUS = [
        'not_started' => '未开始',
        'closed'      => '已取消',
        'inprogress'  => '进行中',
        'final'       => '已完成',
    ];


    /**
     * 镜头统计列表
     * @param Request $request
     * @param int $projectId
     * @param string $entityName
     * @param array $shotStatus
     * @param array $initialEpisodeCode
     * @param array $initialEpisodeCodeList
     * @param array $categoryIdList
     * @param int $yearMonth
     * @param array $storyboardDemandStatus
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function shotTotalList(Request $request, int $projectId, string $entityName, array $shotStatus, array $initialEpisodeCode, array $initialEpisodeCodeList, array $categoryIdList, int $yearMonth, array $storyboardDemandStatus): array
    {
        $res = [];
        $publishedStatusIds = StatusService::getInstance()->byCodeMap([
            'not_started',
            'ready_to_publish',
            'closed',
        ], true ,'id');
        $query = EntityModel::where([
            'entity.module_id' => ModuleService::getInstance()->byCode('shot', 'id'),
            'entity.tenant_id' => $request->getTenantId(),
        ])
            ->field([
                'count(entity.id) as total_shot_num',
                'count((get_json_string(`entity`.`json`, "$.shot_status") in (' . $publishedStatusIds . ')) or null) as final_shot_num',
                'count((get_json_string(`entity`.`json`, "$.shot_perform_form") = "文戏") or null) as literary_drama',
                'count((get_json_string(`entity`.`json`, "$.shot_perform_form") = "文戏" and get_json_string(`entity`.`json`, "$.shot_status") in (' . $publishedStatusIds . ')) or null) as literary_final',
                'count((get_json_string(`entity`.`json`, "$.shot_perform_form") = "武戏") or null) as martial_arts',
                'count((get_json_string(`entity`.`json`, "$.shot_perform_form") = "武戏" and get_json_string(`entity`.`json`, "$.shot_status") in (' . $publishedStatusIds . ')) or null) as martial_arts_final',
                'count((get_json_string(`entity`.`json`, "$.shot_cloth_solution") <> "不需要解算") or null) as solve_shots',
                'count((get_json_string(`entity`.`json`, "$.shot_cloth_solution") <> "不需要解算" and get_json_string(`entity`.`json`, "$.shot_status") in (' . $publishedStatusIds . ')) or null) as solve_shots_final',
                'count((get_json_string(`entity`.`json`, "$.shot_fx") <> "无特效") or null) as special_effects_shots',
                'count((get_json_string(`entity`.`json`, "$.shot_fx") <> "无特效" and get_json_string(`entity`.`json`, "$.shot_status") in (' . $publishedStatusIds . ')) or null) as special_effects_shots_final',
            ]);

        if (!empty($projectId)) {
            $query = $query->where('entity.project_id', $projectId);
        }
        if (!empty($entityName)) {
            $query = $query->where('entity.name', 'LIKE', '%' . $entityName . '%');
        }
        if (!empty($shotStatus)) {
            $statusIds = StatusService::getInstance()->byCodeMap($shotStatus, false, 'id');
            $query = $query->whereRaw('get_json_string(`entity`.`json`, "$.shot_status") IN (' . $statusIds . ')');
        }
        if (empty($initialEpisodeCodeList) && !empty($projectId) && !empty($yearMonth) && !empty($categoryIdList)) {
            $episodeCodeList = $this->getYearMonthEpisodeStepCategoryPlan($yearMonth, $projectId, $categoryIdList);
            if (empty($episodeCodeList)) {
                return $res;
            }
            foreach ($episodeCodeList as $key => $item) {
                $initialEpisodeCodeList[$key]['initial_episode_code'] = $item;
            }
        }

        if (!empty($initialEpisodeCodeList)) {
            $query = $query->whereRaw(EntityService::getInstance()->initialEpisodeCodeSql($initialEpisodeCodeList, "initial_episode_code", "showings_number"));
        }
        // 需求状态
        if (!empty($storyboardDemandStatus)) {
            $query = $query->whereFieldRaw('get_json_string(`entity`.`json`, "$.storyboard_demand_status")', 'in', '(' . implode(',', $storyboardDemandStatus) . ')');
        }

//        if (!empty($categoryIdList)) {
//            $query = $query->whereIn('task.step_category_id', $categoryIdList);
//        }
        $entityList = $query->find();

        $res[] = [
            'total_num' => $entityList['total_shot_num'],
            'final_num' => $entityList['final_shot_num'],
            'category_name' => '总镜头数',
        ];
        $res[] = [
            'total_num' => $entityList['literary_drama'],
            'final_num' => $entityList['literary_final'],
            'category_name' => '文戏',
        ];
        $res[] = [
            'total_num' => $entityList['martial_arts'],
            'final_num' => $entityList['martial_arts_final'],
            'category_name' => '武戏',
        ];
        $res[] = [
            'total_num' => $entityList['solve_shots'],
            'final_num' => $entityList['solve_shots_final'],
            'category_name' => '解算镜头',
        ];
        $res[] = [
            'total_num' => $entityList['special_effects_shots'],
            'final_num' => $entityList['special_effects_shots_final'],
            'category_name' => '特效镜头',
        ];
        return result_array_construct([
            'total_num' => 'intval',
            'final_num' => 'intval',
            'category_name' => 'strval',
        ], $res);
    }

    /**
     * 查询月度剧集环节计划的剧集列表
     * @param int $yearMonth
     * @param int $projectId
     * @param array $categoryIdList
     * @return array
     */
    private function getYearMonthEpisodeStepCategoryPlan(int $yearMonth, int $projectId, array $categoryIdList): array
    {
        $dateRange = get_month_time($yearMonth);
        return PlanModel::join('task_relation_tag', 'plan.task_id = task_relation_tag.task_id')
            ->join('entity', 'entity.id = plan.entity_id')
            ->where([
                'plan.project_id' => $projectId,
                'plan.type' => 'step_category',
                'plan.scope' => 'single',
            ])
            ->whereIn('task_relation_tag.link_module_code', ['step_category', 'show_step_category'])
            ->whereIn('task_relation_tag.link_id', $categoryIdList)
            ->whereBetween('plan_end_time', [$dateRange['sdate'], $dateRange['edate']])
            ->group('entity.code')
            ->column('entity.code');
    }

    /**
     * 获取项目团队成员
     * @param $userId
     * @param $tenantId
     * @param $roleCodeList
     * @param $projectCorrespond
     * @return array
     */
    public function getUserProjectIdListByRoleCode($userId, $tenantId, $roleCodeList, $projectCorrespond): array
    {
        $db = ProjectMemberModel::field('project_member.project_id')
            ->join('project p', 'p.id = project_member.project_id')
            ->where('project_member.user_id', $userId);
        if (!empty($roleCodeList)) {
            $db->whereIn('project_member.role_code', $roleCodeList);
        }
        if (!empty($tenantId)) {
            $db->where('p.tenant_id', $tenantId);
        }
        if (!empty($projectCorrespond)) {
            $projectCorrespond = "'" . implode("','", explode(',', join(',', $projectCorrespond))) . "'";
            $db->whereRaw("get_json_string(p.`json`,'project_status') IN (select id from status where correspond IN ($projectCorrespond))");
        }
        $list = $db->group('project_member.project_id')->column('project_member.project_id');
        if (empty($list)) {
            return [];
        }
        return $list;
    }

    /**
     * 资产统计列表
     * @param $param
     * @param $tenantId
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function assetTotalList($param, $tenantId): array
    {
        $publishedStatusIds = []; // 已发布状态
        foreach (['not_started', 'ready_to_publish', 'closed'] as $code) {
            $publishedStatusIds[] = StatusService::getInstance()->byCode($code)['id'];
        }
        $publishedStatusIds = join(',', $publishedStatusIds);

        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $query = (new \app\model\EntityModel)->field([
            "get_json_string(entity.`json`, 'design_category') as category_name",
            "count((get_json_string(entity.`json`, 'design_grade') = 'S+') or null) as `S+`",
            "count((get_json_string(entity.`json`, 'design_grade') = 'S') or null) as `S`",
            "count((get_json_string(entity.`json`, 'design_grade') = 'A') or null) as `A`",
            "count((get_json_string(entity.`json`, 'design_grade') = 'B') or null) as `B`",
            "count((get_json_string(entity.`json`, 'design_grade') = 'C') or null) as `C`",
            "count((get_json_string(entity.`json`, 'design_status') IN ({$publishedStatusIds})) or null) as `final_num`",
            "count(entity.id) as `total_num`",
        ])
            ->where('entity.module_id', ModuleService::getInstance()->byCode('design')['id'])
            ->whereRaw("get_json_string(`entity`.`json`, '$.design_status') != $closedStatusId")
            ->where("entity.tenant_id", $tenantId);

        $entityList = $this->getAssetTotalQueryWhere($param, $query)->group('category_name')->select();
        if (empty($entityList->toArray())) {
            return [];
        }
        return array_column($entityList->toArray(), null, 'category_name');
    }

    /**
     * @param $param
     * @param $query
     * @return array|mixed
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getAssetTotalQueryWhere($param, $query)
    {
        if (!empty($param['project_id'])) {
            $query->where('entity.project_id', $param['project_id']);
        }
        if (!empty($param['entity_name'])) {
            $query->whereLike('entity.`name`', '%' . $param['entity_name'] . '%');
        }
        if (!empty($param['asset_status'])) {
            $assetStatusIds = [];
            foreach ($param['asset_status'] as $code) {
                $assetStatusIds[] = StatusService::getInstance()->byCode($code)['id'];
            }
            $assetStatusIds = join(',', $assetStatusIds);
            $query->whereRaw("get_json_string(entity.`json`, 'design_status') IN ({$assetStatusIds})");
        }
        // 资产需求状态
        if (!empty($param['design_demand_status'])) {
            $designDemandStatus = join(',', $param['design_demand_status']);
            $query->whereRaw("get_json_string(entity.`json`, 'design_demand_status') IN ({$designDemandStatus})");
        }
        // 等级
        if (!empty($param['design_grade'])) {
            $designGrade = join(',', $param['design_grade']);
            $query->whereRaw("get_json_string(entity.`json`, 'design_grade') IN ({$designGrade})");
        }
        //  资产类型
        if (!empty($param['design_category'])) {
            $designCategory = join(',', $param['design_category']);
            $query->whereRaw("get_json_string(entity.`json`, 'design_category') IN ({$designCategory})");
        }
        // 关联
        if (!empty($param['is_relate'])) {
            // 已关联
            if ($param['is_relate'] == 'yes') {
                $query->whereRaw("get_json_string(entity.`json`, 'design_relate_level') !=''");
            }
            // 未关联
            if ($param['is_relate'] == 'no') {
                $query->whereRaw("get_json_string(entity.`json`, 'design_category') = '场景'");
                $query->whereRaw("get_json_string(entity.`json`, 'design_relate_level') =''");
            }
        }
        // 组织兜底集code
        if (empty($param['initial_episode_code_list']) && !empty($param['project_id']) && !empty($param['year_month']) && !empty($param['category_id_list'])) {
            $episodeCodeList = NeedProjectService::getInstance()->getYearMonthEpisodeStepCategoryPlan($param['year_month'], $param['project_id'], $param['category_id_list']);
            $tmpInitialEpisodeCodeList = [];
            foreach ($episodeCodeList as $code) {
                $tmpInitialEpisodeCodeList[] = [
                    'initial_episode_code' => $code,
                    'showings_number' => [],
                ];
            }
            $param['initial_episode_code_list'] = $tmpInitialEpisodeCodeList;
        }
        if (!empty($param['initial_episode_code_list'])) {
            $episodeName = [];
            foreach ($param['initial_episode_code_list'] as $codeData) {
                $episodeName[] = $codeData['initial_episode_code'];
            }
            // 本集使用
            if (!empty($param['episode_type']) && $param['episode_type'] == 'episode_use') {
                $episodeList = EntityModel::field("id,code")
                    ->where('project_id', $param['project_id'])
                    ->where('module_id', ModuleService::getInstance()->byCode('episode')['id'])
                    ->whereIn('code', join(',', $episodeName))
                    ->select();
                $episodeMap = [];
                if (!empty($episodeList->toArray())) {
                    $episodeMap = array_column($episodeList->toArray(), null, 'id');
                }
                foreach ($param['initial_episode_code_list'] as &$itemData) {
                    $itemData['initial_episode_id'] = $episodeMap[$itemData['id']];
                }
                $sql = EpisodeAllocationService::getInstance()->getEpisodeShowingsSql($param['initial_episode_code_list'], 'episode_id', 'showings_number');
                $relationEntityList = EntityEpisodeRelationModel::field('entity_id')->whereRaw($sql)->select();
                $entityIds = [];
                if (!empty($relationEntityList->toArray())) {
                    $entityIds = array_column($relationEntityList->toArray(), null, 'id');
                    $entityIds = join(',', $entityIds);
                }
                $query->whereIn('entity.id', $entityIds);
            } else {
                $sql = EpisodeAllocationService::getInstance()->getEpisodeShowingsSql($param['initial_episode_code_list'], 'initial_episode_code', 'showings_number');
                $query->whereRaw($sql);
            }
        }
        return $query;
    }
}
